﻿// ================================================================================
// 		File: PatyEduSpecial.cs
// 		Desc: 
//  
// 		Called by:   
//               
// 		Auth: chenwenzhi（--）
// 		Date: 2015-12-27 22:18
// ================================================================================
// 		Change History
// ================================================================================
// 		Date:		Author:				Description:
// 		--------	--------			-------------------
//    
// ================================================================================
// ================================================================================

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Text;
using ZHDJ.Core.Util;
using ZHDJ.Data.Models.Box;

namespace ZHDJ.Data.Models.Org
{
    /// <summary>
    /// 人员表
    /// </summary>
    public class PartyOrgMember : IKeyID
    {
        #region 字段属性
        /// <summary>
        /// ID
        /// </summary>

        public string ID { get; set; }

        /// <summary>
        /// 用户标识
        /// </summary>
        [Key]
        public string LoginID { get; set; }

        /// <summary>
        /// 用户名(用户账号) --用于登陆
        /// </summary>
        public string UserNo { get; set; }

        /// <summary>
        /// 姓名
        /// </summary>
        public string UserName { get; set; }

        /// <summary>
        /// 手机
        /// </summary>
        public string MobileNo { get; set; }

        public string Telephone { get; set; }

        /// <summary>
        /// 身份证
        /// </summary>
        public string IdentityCard { get; set; }

        /// <summary>
        /// Email
        /// </summary>
        public string Email { get; set; }

        /// <summary>
        /// BranchID
        /// </summary>
        public string BranchID { get; set; }

        /// <summary>
        /// 组织负责人
        /// </summary>
        public string BranchRight { get; set; }

        /// <summary>
        /// 职务
        /// </summary>
        public string Post { get; set; }

        /// <summary>
        /// 职位   cyj20150613
        /// </summary>
        public string Position { get; set; }

        /// <summary>
        /// 生日
        /// </summary>

        public DateTime? Birthday { get; set; }

        /// <summary>
        /// 毕业院校
        /// </summary>
        public string FullTimeGreduateSchool { get; set; }

        /// <summary>
        /// 专业
        /// </summary>
        public string FullTimeMajor { get; set; }
        /// <summary>
        /// 学历
        /// </summary>

        public string Educational { get; set; }
        /// <summary>
        /// 在职教育毕业院校
        /// </summary>
        public string PartTimeSchool { get; set; }
        /// <summary>
        /// 在职教育专业
        /// </summary>

        public string PartTimeMajor { get; set; }
        /// <summary>
        /// 第二学历
        /// </summary>

        public string SecondEducaional { get; set; }

        /// <summary>
        /// 籍贯
        /// </summary>

        public string NativePlace { get; set; }

        /// <summary>
        /// 户口所在地
        /// </summary>
        public string HukouPlace { get; set; }

        /// <summary>
        /// 民族
        /// </summary>

        public string Nation { get; set; }

        /// <summary>
        /// 政治面貌 正式党员:1 预备党员:2 未知:3
        /// </summary>

        public string PoliticsLineament { get; set; }

        /// <summary>
        /// 入党时间
        /// </summary>

        public DateTime? JoinPartyDate { get; set; }

        /// <summary>
        /// 入党所在组织
        /// </summary>
        public string JoinPartyOrg { get; set; }

        /// <summary>
        /// 转正时间
        /// </summary>

        public DateTime? BeRegularDate { get; set; }

        /// <summary>
        /// 用户状态
        /// </summary>

        public string EmployeeStatus { get; set; }

        /// <summary>
        /// 奖惩情况
        /// </summary>

        public string RewardsPunishment { get; set; }

        /// <summary>
        /// 民主评议情况
        /// </summary>

        public string DemocraticAppraisal { get; set; }


        /// <summary>
        /// QQNumber
        /// </summary>

        public string QQNumber { get; set; }

        /// <summary>
        /// 是否第一次登陆
        /// </summary>
        public bool? IsFirstLogin { get; set; }




        /// <summary>
        /// 地址
        /// </summary>

        public string Address { get; set; }


        /// <summary>
        /// 性别 0男 1 女
        /// </summary>

        public string Gender { get; set; }
        public string GenderFormat { get { if (Gender == "0") return "男"; else return "女"; } }
        /// <summary>
        /// OrderNo
        /// </summary>

        public int OrderNo { get; set; }
        /// <summary>
        /// ImageUrl
        /// </summary>

        public string ImageUrl { get; set; }
        /// <summary>
        /// Password
        /// </summary>

        public string Password { get; set; }

        /// <summary>
        /// 是否党务工作着
        /// </summary>
        public bool? IsPartyWorker { get; set; }

        /// <summary>
        /// 是否在职
        /// </summary>
        public bool? IsIncumbency { get; set; }
        /// <summary>
        /// IsAgree
        /// </summary>

        public bool? IsAgree { get; set; }

        /// <summary>
        /// 飞信号
        /// </summary>

        public string feitionNo { get; set; }

        /// <summary>
        /// 是否志愿者
        /// </summary>
        public bool? isVolunteer { get; set; }

        /// <summary>
        /// 志愿者编号
        /// </summary>
        public string VolunteerNo { get; set; }

        /// <summary>
        /// 是否党代表
        /// </summary>
        public bool? isPartyDeputy { get; set; }

        /// <summary>
        /// 是否人大代表
        /// </summary>
        public bool? isDeputytoNPC { get; set; }
        /// <summary>
        /// 是否政协委员
        /// </summary>
        public bool? isCPPCC { get; set; }

        /// <summary>
        /// 是否困难党员
        /// </summary>
        public bool? isHardParty { get; set; }

        /// <summary>
        /// 是否离退休党员
        /// </summary>
        public bool? isRetireParty { get; set; }

        /// <summary>
        /// 困难党员备注
        /// </summary>
        public string HardPartyDescription { get; set; }

        /// <summary>
        /// 是否困难党员
        /// </summary>
        public DateTime? pwdOverTime { get; set; }

        /// <summary>
        /// 党员类型
        /// 0:普通
        /// 2:流出
        /// -1:浏览账号
        /// </summary>
        public string runSate { get; set; }

        /// <summary>
        /// 微信帐号
        /// </summary>
        public string WeiXinNo { get; set; }
        /// <summary>
        /// 工作地址
        /// </summary>
        public string WorkAddress { get; set; }

        /// <summary>
        /// 户籍地址
        /// </summary>
        public string CensusAddress { get; set; }
        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime? CreateTime { get; set; }

        /// <summary>
        /// 修改时间
        /// </summary>
        public DateTime? ModifyTimeEx { get; set; }

        /// <summary>
        /// 工作单位
        /// </summary>
        public string WorkUnit { get; set; }

        /// <summary>
        /// 参加工作时间
        /// </summary>
        public DateTime? BeginWorkTime { get; set; }
        /// <summary>
        /// 单位职务
        /// </summary>
        public string UnitJob { get; set; }
        /// <summary>
        /// 现从事职业
        /// </summary>
        public string JobType { get; set; }

        /// <summary>
        /// 组织关系转入
        /// </summary>
        public string OrgRelationshipType { get; set; }

        /// <summary>
        /// 是否村代表
        /// </summary>
        public bool? IsFaipule { get; set; }
        /// <summary>
        /// 出生地
        /// </summary>
        public string Birthplace { get; set; }
        /// <summary>
        /// 学历或职称
        /// </summary>
        public string Title { get; set; }
        /// <summary>
        /// 专长
        /// </summary>
        public string Expertise { get; set; }

        public virtual ICollection<Role> Roles { get; set; }
        public virtual ICollection<Title> Titles { get; set; }

        /// <summary>
        /// 管理的组织IDs
        /// </summary>
        [NotMapped]
        public List<string> ManageBranchIDs
        {
            get; set;
        }
        /// <summary>
        /// 登陆日志ID
        /// </summary>
        [NotMapped]
        public string UserLogID { get; set; }

        #endregion

        #region 数据查询

        /// <summary>
        /// 根据组织ID获取用户
        /// </summary>
        /// <returns></returns>
        public static DataSet GetMemberByBranchId(string branchId, string key, int pageIndex = 0, int pageSize = 20,
            int? runSate = null, bool? isPartyWorker = null, string widthOutUser = "",
            bool? isHardParty = null,
            bool? isPartyDeputy = null,
            bool? isVolunteer = null,
            bool? isIncumbency = null,
            bool? IsFaipule = null)
        {
            var para = new List<MySqlParameter>
            {
                new MySqlParameter() {DbType = DbType.Int32, ParameterName = "Begin", Value = (pageIndex)*pageSize},
                new MySqlParameter() {DbType = DbType.Int32, ParameterName = "PageSize", Value = pageSize}
            };
            var sql = new StringBuilder(@"SELECT SQL_CALC_FOUND_ROWS ID, LoginID,UserName,IsFaipule,runSate,Gender,ImageUrl,
                                MobileNo,CreateTime,isPartyWorker,IdentityCard,cast(JoinPartyDate as char) JoinPartyDate,case isPartyWorker when 1 then '是' else '否' end isPartyWorkerName FROM tblpartyorgmember 
                            WHERE 1=1   ");
            if (!string.IsNullOrEmpty(key))
            {
                sql.AppendFormat(" and (UserName like ?key or MobileNo like ?key or IdentityCard like ?key )");
                para.Add(new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "key",
                    Value = string.Format("%{0}%", key)
                });
            }
            if (isPartyWorker != null)
            {
                sql.AppendFormat(" and isPartyWorker =?isPartyWorker");
                para.Add(new MySqlParameter()
                {
                    DbType = DbType.Boolean,
                    ParameterName = "isPartyWorker",
                    Value = isPartyWorker
                });
            }
            if (isHardParty != null)
            {
                sql.AppendFormat(" and isHardParty =?isHardParty");
                para.Add(new MySqlParameter()
                {
                    DbType = DbType.Boolean,
                    ParameterName = "isHardParty",
                    Value = isHardParty
                });
            }
            if (isPartyDeputy != null)
            {
                sql.AppendFormat(" and isPartyDeputy =?isPartyDeputy");
                para.Add(new MySqlParameter()
                {
                    DbType = DbType.Boolean,
                    ParameterName = "isPartyDeputy",
                    Value = isPartyDeputy
                });
            }
            if (isVolunteer != null)
            {
                sql.AppendFormat(" and isVolunteer =?isVolunteer");
                para.Add(new MySqlParameter()
                {
                    DbType = DbType.Boolean,
                    ParameterName = "isVolunteer",
                    Value = isVolunteer
                });
            }
            if (IsFaipule != null)
            {
                sql.AppendFormat(" and IsFaipule =?IsFaipule");
                para.Add(new MySqlParameter()
                {
                    DbType = DbType.Boolean,
                    ParameterName = "IsFaipule",
                    Value = IsFaipule
                });
            }
            if (isIncumbency != null)
            {
                sql.AppendFormat(" and IsIncumbency =?IsIncumbency");
                para.Add(new MySqlParameter()
                {
                    DbType = DbType.Boolean,
                    ParameterName = "IsIncumbency",
                    Value = isIncumbency
                });
            }
            if (runSate != null)
            {
                sql.AppendFormat(" and runSate=?runSate ");
                para.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "runSate", Value = runSate });
            }
            if (!string.IsNullOrEmpty(branchId))
            {
                sql.AppendFormat(" and BranchID=?BranchID ");
                para.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchID", Value = branchId });
            }
            if (!string.IsNullOrEmpty(widthOutUser))
            {
                sql.AppendFormat(" and ( loginid not in ({0}) )", widthOutUser);
            }
            sql.Append(" ORDER BY OrderNo desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sql.ToString(), para.ToArray());
        }

        /// <summary>
        /// 根据组织ID获取用户(注册用户专用，根据创建时间进行排序)
        /// </summary>
        /// <returns></returns>
        public static DataSet GetMemberByBranchId1(string branchId, string key, int pageIndex = 0, int pageSize = 20,
            int? runSate = null, bool? isPartyWorker = null, string widthOutUser = "",
            bool? isHardParty = null,
            bool? isPartyDeputy = null,
            bool? isVolunteer = null,
            bool? isIncumbency = null,
            bool? IsFaipule = null)
        {
            var para = new List<MySqlParameter>
            {
                new MySqlParameter() {DbType = DbType.Int32, ParameterName = "Begin", Value = (pageIndex)*pageSize},
                new MySqlParameter() {DbType = DbType.Int32, ParameterName = "PageSize", Value = pageSize}
            };
            var sql = new StringBuilder(@"SELECT SQL_CALC_FOUND_ROWS ID, LoginID,UserName,IsFaipule,runSate,Gender,ImageUrl,
                                MobileNo,CreateTime,isPartyWorker,IdentityCard,cast(JoinPartyDate as char) JoinPartyDate,case isPartyWorker when 1 then '是' else '否' end isPartyWorkerName FROM tblpartyorgmember 
                            WHERE 1=1   ");
            if (!string.IsNullOrEmpty(key))
            {
                sql.AppendFormat(" and (UserName like ?key or MobileNo like ?key or IdentityCard like ?key )");
                para.Add(new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "key",
                    Value = string.Format("%{0}%", key)
                });
            }
            if (isPartyWorker != null)
            {
                sql.AppendFormat(" and isPartyWorker =?isPartyWorker");
                para.Add(new MySqlParameter()
                {
                    DbType = DbType.Boolean,
                    ParameterName = "isPartyWorker",
                    Value = isPartyWorker
                });
            }
            if (isHardParty != null)
            {
                sql.AppendFormat(" and isHardParty =?isHardParty");
                para.Add(new MySqlParameter()
                {
                    DbType = DbType.Boolean,
                    ParameterName = "isHardParty",
                    Value = isHardParty
                });
            }
            if (isPartyDeputy != null)
            {
                sql.AppendFormat(" and isPartyDeputy =?isPartyDeputy");
                para.Add(new MySqlParameter()
                {
                    DbType = DbType.Boolean,
                    ParameterName = "isPartyDeputy",
                    Value = isPartyDeputy
                });
            }
            if (isVolunteer != null)
            {
                sql.AppendFormat(" and isVolunteer =?isVolunteer");
                para.Add(new MySqlParameter()
                {
                    DbType = DbType.Boolean,
                    ParameterName = "isVolunteer",
                    Value = isVolunteer
                });
            }
            if (IsFaipule != null)
            {
                sql.AppendFormat(" and IsFaipule =?IsFaipule");
                para.Add(new MySqlParameter()
                {
                    DbType = DbType.Boolean,
                    ParameterName = "IsFaipule",
                    Value = IsFaipule
                });
            }
            if (isIncumbency != null)
            {
                sql.AppendFormat(" and IsIncumbency =?IsIncumbency");
                para.Add(new MySqlParameter()
                {
                    DbType = DbType.Boolean,
                    ParameterName = "IsIncumbency",
                    Value = isIncumbency
                });
            }
            if (runSate != null)
            {
                sql.AppendFormat(" and runSate=?runSate ");
                para.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "runSate", Value = runSate });
            }
            if (!string.IsNullOrEmpty(branchId))
            {
                sql.AppendFormat(" and BranchID=?BranchID ");
                para.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchID", Value = branchId });
            }
            if (!string.IsNullOrEmpty(widthOutUser))
            {
                sql.AppendFormat(" and ( loginid not in ({0}) )", widthOutUser);
            }
            sql.Append(" ORDER BY CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sql.ToString(), para.ToArray());
        }

        public static bool CheckRunSate(string loginID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" select runSate from tblpartyorgmember where LoginID=?LoginID ");
            List<MySqlParameter> parameters = new List<MySqlParameter>();
            parameters.Add(
                new MySqlParameter() { DbType = DbType.String, ParameterName = "LoginID", Value = string.Format("{0}", loginID) });
            return MySqlHelper.ExecuteScalar(sb.ToString(), parameters.ToArray()).ToString().Equals("0");
        }

        public static string QueryMemberMobileNo(string loginID)
        {
            string sql = @"SELECT MobileNo FROM dbo.tblpartyorgmember WHERE LoginID=?LoginID";
            List<MySqlParameter> parameters = new List<MySqlParameter>();
            parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "LoginID", Value = loginID });
            Object obj = MySqlHelper.ExecuteScalar(sql, parameters.ToArray());
            if (obj != null) return obj.ToString();
            else return "";
        }

        public static string QueryMemberBranchID(string loginID)
        {
            string sql = @"SELECT BranchID FROM tblpartyorgmember WHERE LoginID=?LoginID";
            List<MySqlParameter> parameters = new List<MySqlParameter>();
            parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "LoginID", Value = loginID });
            Object obj = MySqlHelper.ExecuteScalar(sql, parameters.ToArray());
            if (obj != null) return obj.ToString();
            else return "";
        }

        public static int QueryMemberCountByBranchID(string branchID, int? runSate = null)
        {
            string sql = @"SELECT count(*) FROM tblpartyorgmember WHERE BranchID=?BranchID";
            List<MySqlParameter> parameters = new List<MySqlParameter>();
            parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchID", Value = branchID });

            if (runSate != null)
            {
                sql += " and runSate=?runSate ";
                parameters.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "runSate", Value = runSate });
            }

            Object obj = MySqlHelper.ExecuteScalar(sql, parameters.ToArray());
            if (obj != null) return Convert.ToInt32(obj);
            else return 0;
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改用户是否是第一次登录
        /// </summary>
        /// <param name="LoginID"></param>
        /// <param name="isFirstLogin"></param>
        public static void UpdateIsFirstLogin(string LoginID, int isFirstLogin)
        {
            string sql = @" UPDATE `tblpartyorgmember` SET `IsFirstLogin`='" + isFirstLogin + "' WHERE (`LoginID`='" + LoginID + "') ";
            MySqlHelper.ExecuteNonQuery(sql.ToString());
        }

        #endregion

        #region 数据导入

        public static bool SaveImport(DataTable dt)
        {
            StringBuilder sbsql = new StringBuilder();
            int i = 0;
            List<MySqlParameter> parameters = new List<MySqlParameter>();
            DateTime tempDateTime = DateTime.Now;
            foreach (DataRow dr in dt.Rows)
            {
                sbsql.AppendFormat(@"INSERT INTO tblpartyorgmember(LoginID,ID,UserNo,UserName,BranchID,Gender,
                                        IsPartyWorker,Birthday,NativePlace,Nation,JoinPartyDate,BeRegularDate,MobileNo,IdentityCard,isAgree,runSate,CreateTime,IsFirstLogin,orderno,isvolunteer,ispartydeputy,ishardparty,pwdovertime,Password)
                                        VALUES('{6}','{7}','{8}','{9}','{10}','{11}',{1},'{2}'
                                        ,'{12}','{13}','{3}', '{4}','{14}','{15}',1,0,'{5}',1,0,0,0,0,'9999-12-31 00:00:00','7kOiZwsgoA2wUrlnumEoVSJ0YV7XTvps');", i, dr["党务工作者"].ToString() == "是" ? 1 : 0,
                                        Convert.ToDateTime(dr["Birthday"]).ToString("yyyy-MM-dd"), Convert.ToDateTime(dr["JoinPartyDate"]).ToString("yyyy-MM-dd"),
                                        Convert.ToDateTime(dr["BeRegularDate"]).ToString("yyyy-MM-dd"), tempDateTime.ToString("yyyy-MM-dd"),
                                        StringHelper.NewID, dr["用户名"].ToString(), dr["用户名"].ToString(), dr["姓名"].ToString(), dr["BranchID"].ToString(),
                                        dr["性别"].ToString() == "男" ? 0 : 1, dr["籍贯"].ToString(), dr["民族"].ToString(), dr["手机号码"].ToString(), dr["身份证号"].ToString());
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = string.Format("LoginID{0}", i), Value = StringHelper.NewID });
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = string.Format("ID{0}", i), Value = dr["用户名"].ToString() });
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = string.Format("UserNo{0}", i), Value = dr["用户名"].ToString() });
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = string.Format("UserName{0}", i), Value = dr["姓名"].ToString() });
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = string.Format("BranchID{0}", i), Value = dr["BranchID"].ToString() });
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = string.Format("Gender{0}", i), Value = dr["性别"].ToString() == "男" ? "1" : "0" });
                //parameters.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = string.Format("IsPartyWorker{0}", i), Value = dr["党务工作者"].ToString() == "是" ? 1 : 0 });
                //parameters.Add(new MySqlParameter() { DbType = DbType.DateTime, ParameterName = string.Format("Birthday{0}", i), Value = string.IsNullOrEmpty(dr["Birthday"].ToString()) ? null : Convert.ToDateTime(dr["Birthday"]).ToString() });
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = string.Format("NativePlace{0}", i), Value = dr["籍贯"].ToString() });
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = string.Format("Nation{0}", i), Value = dr["民族"].ToString() });
                //parameters.Add(new MySqlParameter() { DbType = DbType.DateTime, ParameterName = string.Format("JoinPartyDate{0}", i), Value = string.IsNullOrEmpty(dr["JoinPartyDate"].ToString()) ? null : Convert.ToDateTime(dr["JoinPartyDate"]).ToString() });
                //parameters.Add(new MySqlParameter() { DbType = DbType.DateTime, ParameterName = string.Format("BeRegularDate{0}", i), Value = string.IsNullOrEmpty(dr["BeRegularDate"].ToString()) ? null : Convert.ToDateTime(dr["BeRegularDate"]).ToString() });
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = string.Format("MobileNo{0}", i), Value = dr["手机号码"].ToString() });
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = string.Format("IdentityCard{0}", i), Value = dr["身份证号"].ToString() });
                //parameters.Add(new MySqlParameter() { DbType = DbType.DateTime, ParameterName = string.Format("CreateTime{0}", i), Value = tempDateTime });
                i++;
            }
            try
            {
                MySqlHelper.ExecuteNonQuery(sbsql.ToString());
            }
            catch (Exception ex)
            {

                return false;
            }
            return true;
        }
        #endregion
    }
}
